package com.qy127.dao.impl;

import com.qy127.dao.StaffDao;
import com.qy127.entity.Staff;
import com.qy127.util.JDBCUtil;
import org.apache.commons.beanutils.BeanUtils;
import org.springframework.stereotype.Repository;
import java.lang.reflect.InvocationTargetException;
import java.util.List;
import java.util.Map;
@Repository
public class StaffDaoImpl implements StaffDao {

    /**
     * 根据前台发送的staffid 和 password查询用户
     * @param staffId
     * @param password
     * @return
     */
    @Override
    public Staff getStaffByStaffIdAndPassword(String staffId, String password) {
        String sql ="select * from t_staff where staffId = ? and password = ?";
        List<Map<String, Object>> maps = JDBCUtil.queryListMap(sql, staffId, password);
        //System.out.println(maps);
        if (maps.size()>0){
            Staff staff = new Staff();
            Map<String, Object> map = maps.get(0);
            try {
                BeanUtils.populate(staff,map);
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            } catch (InvocationTargetException e) {
                e.printStackTrace();
            }
            return staff;
        }
        return null;
    }

    /**
     * 查询所有在职员工的信息
     * @param staff
     * @return
     */
    @Override
    public List<Map<String,Object>> getAllStaff(Staff staff) {

       /* StringBuilder sql = new StringBuilder("SELECT ts.id, ts.staffId,ts.staffName,ts.`password`\n" +
                ",ts.phone,ts.idCard,ts.address,ts.createdTime,ts.resignedTime,ts.`status`,ts.roleId,tr.rname,\n" +
                "td.deptid,td.dname,ts.url from t_staff ts JOIN t_role tr \n" +
                "ON ts.roleId=tr.rid  JOIN t_dept td  ON ts.deptid=td.deptid where 1=1 ");*/
        StringBuffer sql = new StringBuffer("select st.*,ro.rname,de.dname from t_staff st, t_role ro, t_dept de where st.roleId=ro.rid and st.deptid=de.deptid and st.status=1");
        //System.out.println(staff.getPage()+","+staff.getLimit());
        if(staff.getStaffId()!=null&&staff.getStaffId()!=0){
            sql.append(" and st.staffId like '%").append(staff.getStaffId()+"%' ");
        }
        if(staff.getStaffName()!=null){
            sql.append(" and st.staffName like '%").append(staff.getStaffName()+"%' ");
        }
        if(staff.getRoleId()!=null&&staff.getRoleId()!=0){
            sql.append(" and st.roleId = ").append(staff.getRoleId());
        }
        if(staff.getDeptid()!=null&&staff.getDeptid()!=0){
            sql.append(" and st.deptid = ").append(staff.getDeptid());
        }
        sql.append(" limit ?,? ");
        //System.out.println(sql+","+staff.getPage()+"  "+staff.getLimit());
        List<Map<String, Object>> list = JDBCUtil.queryListMap(sql.toString(),(staff.getPage()-1)*staff.getLimit(),staff.getLimit());
        if (list.size()>0){
            return list;
        }
        return null;
    }

    /**
     * 查询所有离职员工
     * @param staff
     * @return
     */
    @Override
    public List<Map<String,Object>> getStaff(Staff staff) {

       /* StringBuilder sql = new StringBuilder("SELECT ts.id, ts.staffId,ts.staffName,ts.`password`\n" +
                ",ts.phone,ts.idCard,ts.address,ts.createdTime,ts.resignedTime,ts.`status`,ts.roleId,tr.rname,\n" +
                "td.deptid,td.dname,ts.url from t_staff ts JOIN t_role tr \n" +
                "ON ts.roleId=tr.rid  JOIN t_dept td  ON ts.deptid=td.deptid where 1=1 ");*/
        StringBuffer sql = new StringBuffer("select st.*,ro.rname,de.dname from t_staff st, t_role ro, t_dept de where st.roleId=ro.rid and st.deptid=de.deptid and st.status=0");
        //System.out.println(staff.getPage()+","+staff.getLimit());
        if(staff.getStaffId()!=null&&staff.getStaffId()!=0){
            sql.append(" and st.staffId like '%").append(staff.getStaffId()+"%' ");
        }
        if(staff.getStaffName()!=null){
            sql.append(" and st.staffName like '%").append(staff.getStaffName()+"%' ");
        }
        if(staff.getRoleId()!=null&&staff.getRoleId()!=0){
            sql.append(" and st.roleId = ").append(staff.getRoleId());
        }
        if(staff.getDeptid()!=null&&staff.getDeptid()!=0){
            sql.append(" and st.deptid = ").append(staff.getDeptid());
        }
        sql.append(" limit ?,? ");
        //System.out.println(sql+","+staff.getPage()+"  "+staff.getLimit());
        List<Map<String, Object>> list = JDBCUtil.queryListMap(sql.toString(),(staff.getPage()-1)*staff.getLimit(),staff.getLimit());
        if (list.size()>0){
            return list;
        }
        return null;
    }


    /**
     * 查询所有员工数量
     * @param staff
     * @return
     */
    @Override
    public int getAllCount(Staff staff) {

        /*StringBuilder sql = new StringBuilder("SELECT count(*) from\n" +
                "t_staff ts JOIN t_role tr \n" +
                "ON ts.roleId=tr.rid \n" +
                "JOIN t_dept td \n" +
                "ON ts.deptid=td.deptid where 1=1  ");*/
        StringBuffer sql = new StringBuffer("select count(*) from t_staff where 1=1 ");
        if(staff.getStaffId()!=null&&staff.getStaffId()!=0){
            sql.append(" and staffId like '%").append(staff.getStaffId()+"%' ");
        }
        if(staff.getStaffName()!=null&&(!staff.getStaffName().equals("")) ){
            sql.append(" and staffName like '%").append(staff.getStaffName()+"%' ");
        }
        if(staff.getRoleId()!=null&&staff.getRoleId()!=0){
            sql.append(" and roleId = ").append(staff.getRoleId());
        }
        if(staff.getDeptid()!=null&&staff.getDeptid()!=0){
            sql.append(" and deptId = ").append(staff.getDeptid());
        }

        List<Map<String, Object>> list = JDBCUtil.queryListMap(sql.toString());
        if(list.size()>0){
            int i = Integer.parseInt(list.get(0).get("count(*)")+"");
            return i;
        }
        return 0;
    }

    /**
     * 新增员工
     * @param staff
     * @return
     */
    @Override
    public int insertStaff(Staff staff) {

        String sql = "insert into t_staff values(null,?,?,123123,?,?,?,?,null,1,?,?,null)";

        int i = JDBCUtil.update(sql,staff.getStaffId(),staff.getStaffName(),staff.getPhone(),
                staff.getIdCard(),staff.getAddress(),staff.getCreatedTime(),
                staff.getRoleId(),staff.getDeptid());
        return i;

    }

    /**
     * 修改员工状态
     * @param id
     * @param status
     * @return
     */
    @Override
    public int updateStatus(int id, int status) {
        if (status==0){
            status=1;
        }else {
            status=0;
        }
        String sql = "update t_staff set status =? where id = ?";
        int i = JDBCUtil.update(sql, status, id);
        return i;
    }

    /**
     * 修改员工信息
     * @param staff
     * @return
     */
    @Override
    public int updateStaff(Staff staff) {

        String sql = "update t_staff set staffName=?,phone=?,address=?,roleId=?,deptid=? where id=?";
        int update = JDBCUtil.update(sql, staff.getStaffName(),staff.getPhone(), staff.getAddress(),
                staff.getRoleId(), staff.getDeptid(), staff.getId());
        return update;
    }

    @Override
    public List<Map<String, Object>> getAllIdCard() {
        String sql = "select idCard from t_staff";
        List<Map<String, Object>> list = JDBCUtil.queryListMap(sql);

        return list;
    }

    @Override
    public int getMaxStaffId() {
        String sql = "select max(staffId) from t_staff";
        List<List> lists = JDBCUtil.queryList(sql);
        int i = (Integer)lists.get(0).get(0);
        return i;
    }

    @Override
    public int getAllByRoleId(Integer roleId) {
        String sql = "select count(*) from t_staff where roleId = ?";
        List<List> lists = JDBCUtil.queryList(sql, roleId);
        if (lists.size()>0){
            return Integer.parseInt(lists.get(0).get(0)+"");
        }
        return 0;
    }

    @Override
    public int updateUrl(String url,String staffId) {
            String sql = "update t_staff set url=? where staffId=?";
            int i = JDBCUtil.update(sql, url, staffId);
            return i;

    }


}
